package io;

import java.sql.*;
import java.util.ArrayList;

import track.Album;
import track.Artist;
import track.Genre;
import track.Song;
import track.Track;
import track.playlistEntry;

/**
 * DbFuncs is a class for dealing with basic connections to the data base.
 * Since it has only functions which can be called from everywhere in the code, all its 
 * functions are static (There is no point in creating an object of the type DbFuncs).
 * 
 * @author Alex
 *
 */
public class DbFuncs {

	/**
	 * This returns  the content  in the table in the specified tuple
	 * 
	 * @param table
	 * @param retParam
	 * @param condParam
	 * @param searchedParam
	 * 
	 * @return the int value in the tuple
	 */
	public static int getContentInt(String table, String retParam, String condParam, int searchedParam)
	{
		Statement stmt = null;
		try {
			stmt = SqlDb.getConnection().createStatement();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		ResultSet result = getContentHelper(table, retParam, condParam, searchedParam, stmt);
		int ret;
		
		try {
			ret = result.getInt(1);			
			stmt.close();
			result.close();
		}
		catch(Exception e) {
			ret = -1;
		}
		
		return ret;
	}

	/**
	 * This returns  the content  in the table in the specified tuple
	 * 
	 * @param table
	 * @param retParam
	 * @param condParam
	 * @param searchedParam
	 * 
	 * @return the string value in the tuple
	 */
	public static String getContentString(String table, String retParam, String condParam, int searchedParam)
	{
		Statement stmt = null;
		try {
			stmt = SqlDb.getConnection().createStatement();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		ResultSet result = getContentHelper(table, retParam, condParam, searchedParam, stmt);
		String ret;
		
		try {
			ret = result.getString(1);	
			stmt.close();
			result.close();
		}
		catch(Exception e) {
			ret = null;
		}
		
		return ret;
	}
	
	/**
	 * This is a helper function for the getContent functions
	 * 
	 * @param table
	 * @param retParam
	 * @param condParam
	 * @param searchedParam
	 * @param conn

	 * @return the result of the query execute
	 */
	private static ResultSet getContentHelper(String table, String retParam, String condParam, int searchedParam, Statement stmt)
	{
		ResultSet result;
		try {
			result = stmt.executeQuery("SELECT DISTINCT " + retParam +
												" FROM " + table +
												" WHERE " + condParam + " = '" + searchedParam + "'");
			result.next();
		} 
		catch (SQLException e) {
			result = null;
		}

		return result;	
	}

	public static ResultSet getContent(String query)
	{
		Statement stmt;
		ResultSet result;
		try {
			stmt = SqlDb.getConnection().createStatement();
			result = stmt.executeQuery( query );
			result.next();
		} 
		catch (SQLException e) {
			result = null;
		}

		return result;	
	}

	
	public static playlistEntry[] getResult(String string, int numberOfSearchResults) {
		

		
		Statement stmt;
		ResultSet result;
		Track t = null;

		ArrayList<playlistEntry> aPE = new ArrayList<playlistEntry>();
		playlistEntry[] pEs = null;
		
		
		try {
			stmt = SqlDb.getConnection().createStatement();
			string = string.toLowerCase();
			result = stmt.executeQuery( "SELECT tracks.id FROM tracks, artists WHERE tracks.artist_id = artists.id AND " +
					"(LOWER(artists.name) LIKE '%" + string + "%' OR LOWER(tracks.name) LIKE '%" + string + "%') " +
							
							"AND rownum <= 30 ORDER BY dbms_random.value");
			result.next();
			int counter = 0;
			while(!result.isAfterLast() && counter < numberOfSearchResults && (result.next() != false)) {
				counter++;
				t = new Track(result.getInt(1));
				
				aPE.add(new playlistEntry(t.getArtist(),t, t.getDisc(),t.getDisc().getSubGenre().getGenre()));
				
			}
			pEs = new playlistEntry[aPE.size()];
			for(int i=0;i<pEs.length;i++){
				pEs[i] = aPE.get(i);
			}
			stmt.close();
		} 
		catch (SQLException e) {
			System.out.println(e.getMessage());
			result = null;
		}

		return pEs;
	}	
	
}
